﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using AKMII.DMRA.Common;

namespace AKMII.DMRA.DataAccess
{
    public class LeadAccessor
    {
        /// <summary>
        /// Import Lead information into Database.
        /// </summary>
        /// <param name="lead">Lead information from DMRA.</param>
        /// <returns></returns>
        public bool AddLead(Lead lead)
        {
            const string SpName = "sp_Insert_Lead";

            try
            {
                object[] parameters = new object[] 
                {
                    lead.SelPersonId,
                    lead.Zip,
                    lead.LeadBlockCode,
                    lead.Division,
                    lead.Market,
                    string.Empty,
                    string.Empty,
                    lead.CallType,
                    lead.LeadType,
                    lead.MdNumber,
                    lead.SequenceNumber
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in add lead", ex);
            }

            return true;
        }

        public void InsertOptimizedLead(string userId, string leadBlockCode, string leads, int count,
            int activeCount, int lapsedCount, int businessCount, int neighborCount)
        {
            const string SpName = "sp_Insert_OptimizedLeads";

            try
            {
                object[] parameters = new object[] 
                {
                    userId,
                    leadBlockCode,
                    leads,
                    count,
                    activeCount,
                    lapsedCount,
                    businessCount,
                    neighborCount
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in insert optimized lead", ex);
            }
        }

        public List<string> GetOptimizedLead(string userId)
        {
            List<string> optimizedLeads = new List<string>();

            const string SpName = "sp_Get_OptimizedLeads";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { userId };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    optimizedLeads.Add(!reader.IsDBNull(0) ? reader.GetString(0) : string.Empty);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get optimized lead ", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return optimizedLeads;
        }

        public void AddLead(List<Lead> leads)
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Constants.ConnectionString))
            {
                try
                {
                    bulkCopy.DestinationTableName = "dbo.Leads";
                    
                    if (leads != null && leads.Count > 0)
                    {
                        DataTable table = ConstructDataTable();

                        foreach (Lead lead in leads)
                        {
                            object[] parameters = new object[] 
                            {
                                lead.LeadId,
                                lead.SelPersonId,
                                lead.Zip,
                                lead.LeadBlockCode,
                                lead.Division,
                                lead.Market,
                                string.Empty,
                                string.Empty,
                                lead.CallType,
                                lead.LeadType,
                                lead.MdNumber,
                                lead.SequenceNumber,
                                lead.RawData,
                                lead.City,
                                lead.State,
                                lead.County,
                                lead.Latitude,
                                lead.Longitude,
                                lead.ProsLeadCount,
                                lead.PcLeadType,
                                lead.PersonStatus,
                                lead.PreferCity
                            };

                            table.Rows.Add(parameters);
                        }

                        bulkCopy.WriteToServer(table);
                    }
                }
                catch (Exception ex)
                {
                    throw new ApplicationException("Exception occurs in add leads", ex);
                }
            }
        }

        public void DeleteLead()
        {
            const string SpName = "sp_Delete_Lead";

            try
            {
                object[] parameters = new object[] { };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in delete lead", ex);
            }
        }

        public List<Lead> GetLeads(int leadBlockCodeId)
        {
            List<Lead> leads = new List<Lead>();
            const string SpName = "sp_Get_LeadsByLeadBlockCode";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { leadBlockCodeId };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    Lead lead = new Lead();

                    lead.LeadId = reader.GetInt32(0);
                    lead.Id = lead.LeadId;
                    lead.SelPersonId = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                    lead.LeadBlockCode = !reader.IsDBNull(3) ? reader.GetString(3) : string.Empty;
                    lead.Division = !reader.IsDBNull(4) ? reader.GetString(4) : string.Empty;
                    lead.Market = !reader.IsDBNull(5) ? reader.GetString(5) : string.Empty;
                    //lead.Territory = !reader.IsDBNull(6) ? reader.GetString(6) : string.Empty;
                    lead.CallType = !reader.IsDBNull(8) ? reader.GetString(8) : string.Empty;
                    lead.LeadType = !reader.IsDBNull(9) ? reader.GetString(9) : string.Empty;
                    lead.MdNumber = !reader.IsDBNull(10) ? reader.GetString(10) : string.Empty;
                    lead.SequenceNumber = !reader.IsDBNull(11) ? reader.GetString(11) : null;
                    lead.RawData = !reader.IsDBNull(12) ? reader.GetString(12) : string.Empty;

                    lead = GetLeadAdditional(lead);

                    leads.Add(lead);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get leads by lead block", ex);
            }

            return leads;
        }

        public List<Lead> GetLeads(string leadBlockCodeId)
        {
            List<Lead> leads = new List<Lead>();
            const string SpName = "sp_Get_LeadsByLeadBlockCodeId";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { leadBlockCodeId };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    Lead lead = new Lead();
                    
                    lead.LeadId = reader.GetInt32(0);
                    lead.Id = lead.LeadId;
                    lead.SelPersonId = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                    lead.LeadBlockCode = !reader.IsDBNull(3) ? reader.GetString(3) : string.Empty;
                    lead.Division = !reader.IsDBNull(4) ? reader.GetString(4) : string.Empty;
                    lead.Market = !reader.IsDBNull(5) ? reader.GetString(5) : string.Empty;
                    //lead.Territory = !reader.IsDBNull(6) ? reader.GetString(6) : string.Empty;
                    lead.CallType = !reader.IsDBNull(8) ? reader.GetString(8) : string.Empty;
                    lead.LeadType = !reader.IsDBNull(9) ? reader.GetString(9) : string.Empty;
                    lead.MdNumber = !reader.IsDBNull(10) ? reader.GetString(10) : string.Empty;
                    lead.SequenceNumber = !reader.IsDBNull(11) ? reader.GetString(11) : null;
                    lead.RawData = !reader.IsDBNull(12) ? reader.GetString(12) : string.Empty;
                    lead.ZipId = reader.GetInt32(18);

                    lead = GetLeadAdditional(lead);

                    leads.Add(lead);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get leads by lead block id", ex);
            }

            return leads;
        }

        public List<Lead> GetLeads(string accountID, int month, int year, int type)
        {
            List<Lead> leads = new List<Lead>();
            const string SpName = "sp_Get_Leads";
            const string SpNameNew = "sp_Get_AssignedLeads";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { accountID, month.ToString(), year.ToString() };
                reader = SqlHelper.ExecuteReaderNoTimeout(Constants.ConnectionString, 
                    type == 0 ? SpName : SpNameNew, parameters);


                while (reader.Read())
                {
                    Lead lead = new Lead();

                    if (!string.IsNullOrEmpty(accountID))
                    {
                        lead.LeadId = reader.GetInt32(0);
                        lead.Id = lead.LeadId;
                        lead.SelPersonId = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                        lead.LeadBlockCode = !reader.IsDBNull(3) ? reader.GetString(3) : string.Empty;
                        lead.Division = !reader.IsDBNull(4) ? reader.GetString(4) : string.Empty;
                        lead.Market = !reader.IsDBNull(5) ? reader.GetString(5) : string.Empty;
                        //lead.Territory = !reader.IsDBNull(6) ? reader.GetString(6) : string.Empty;
                        lead.CallType = !reader.IsDBNull(8) ? reader.GetString(8) : string.Empty;
                        lead.LeadType = !reader.IsDBNull(9) ? reader.GetString(9) : string.Empty;
                        lead.MdNumber = !reader.IsDBNull(10) ? reader.GetString(10) : string.Empty;
                        lead.SequenceNumber = !reader.IsDBNull(11) ? reader.GetString(11) : null;
                        lead.RawData = !reader.IsDBNull(12) ? reader.GetString(12) : string.Empty;
                        lead.UserId = !reader.IsDBNull(18) ? reader.GetString(18) : string.Empty;

                        lead = GetLeadAdditional(lead);
                    }
                    else
                    {
                        lead.RawData = !reader.IsDBNull(0) ? reader.GetString(0) : null;
                    }

                    leads.Add(lead);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get leads", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return leads;
        }

        public List<Pair<string, string>> GetCity()
        {
            List<Pair<string, string>> userCityPairs = new List<Pair<string, string>>();

            const string SpName = "sp_Get_City";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    Pair<string, string> pair = new Pair<string, string>(!reader.IsDBNull(0) ? reader.GetString(0) : string.Empty,
                        !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty);
                    userCityPairs.Add(pair);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get city", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return userCityPairs;
        }

        public List<string> GetCity(string mdnumber, string county)
        {
            List<string> cities = new List<string>();

            const string SpName = "sp_Get_CityByMdAndCounty";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { mdnumber, county };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    cities.Add(!reader.IsDBNull(0) ? reader.GetString(0) : string.Empty);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get city by mdnumber and county", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return cities;
        }

        public List<Pair<string, string>> GetCounty()
        {
            List<Pair<string, string>> userCountyPairs = new List<Pair<string, string>>();
            const string SpName = "sp_Get_County";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    Pair<string, string> pair = new Pair<string, string>(!reader.IsDBNull(0) ? reader.GetString(0) : string.Empty,
                        !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty);
                    userCountyPairs.Add(pair);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get county", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return userCountyPairs;
        }

        /*
        public List<Pair<string, string>> GetTerritory(string mdNumber, int month, int year)
        {
            List<Pair<string, string>> userTerritoryPairs = new List<Pair<string, string>>();
            const string SpName = "sp_Get_Territory";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { mdNumber, month, year };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    Pair<string, string> pair = new Pair<string, string>(!reader.IsDBNull(0) ? reader.GetString(0) : string.Empty,
                        !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty);
                    userTerritoryPairs.Add(pair);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get territory", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return userTerritoryPairs;
        }
        */
        public List<Pair<string, string>> GetState()
        {
            List<Pair<string, string>> userStatePairs = new List<Pair<string, string>>();
            const string SpName = "sp_Get_State";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    Pair<string, string> pair = new Pair<string, string>(!reader.IsDBNull(0) ? reader.GetString(0) : string.Empty,
                        !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty);
                    userStatePairs.Add(pair);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get state", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return userStatePairs;
        }

        public string GetRawData(Lead lead)
        {
            string rawData = string.Empty;
            const string SpName = "sp_Get_RawData";

            try
            {
                object[] parameters = new object[] 
                {
                    lead.SequenceNumber,
                    lead.LeadBlockCode,
                    lead.LeadType,
                    ((string.IsNullOrEmpty(lead.FirmName) ? string.Empty : lead.FirmName).PadRight(30) + (string.IsNullOrEmpty(lead.StreetAddress) ? string.Empty : lead.StreetAddress).PadRight(30) + (string.IsNullOrEmpty(lead.BirthDate) ? string.Empty : lead.BirthDate).PadRight(7) + (string.IsNullOrEmpty(lead.FirstName) ? string.Empty : lead.FirstName) + " " + (string.IsNullOrEmpty(lead.LastName) ? string.Empty : lead.LastName))
                };

                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString, SpName, parameters);

                if (o != null)
                {
                    rawData = o.ToString();
                }
                else
                {
                    Logger.Warn("Empty line:" + lead.SequenceNumber + "MD:" + ((string.IsNullOrEmpty(lead.FirmName) ? string.Empty : lead.FirmName).PadRight(30) + (string.IsNullOrEmpty(lead.StreetAddress) ? string.Empty : lead.StreetAddress).PadRight(30) + (string.IsNullOrEmpty(lead.BirthDate) ? string.Empty : lead.BirthDate).PadRight(7) + (string.IsNullOrEmpty(lead.FirstName) ? string.Empty : lead.FirstName) + " " + (string.IsNullOrEmpty(lead.LastName) ? string.Empty : lead.LastName)));
                }
            }
            catch (Exception ex)
            {
                Logger.Error("sql");
                throw new ApplicationException("Exception in get raw data ", ex);
            }

            return rawData;
        }

        public int GetOrderedLeadsCount()
        {
            int count = 0;
            const string SpName = "sp_Get_OrderedLeadsCount";

            try
            {
                object[] parameters = new object[] { };
                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString, SpName, parameters);

                if (o != null)
                {
                    count = Convert.ToInt32(o);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get ordered leads count", ex);
            }

            return count;
        }

        public void InsertOrderedLeads(int month, int year)
        {
            const string SpName = "sp_Insert_OrderedLeads";

            try
            {
                object[] parameters = new object[] { month.ToString(), year.ToString() };
                SqlHelper.ExecuteNonQueryNoTimeout(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in insert ordered leads", ex);
            }
        }

        public void RemoveOrderedLeads()
        {
            const string SpName = "sp_Remove_OrderedLeads";

            try
            {
                object[] parameters = new object[] { };
                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in remove ordered leads", ex);
            }
        }

        public List<Lead> GetOrderedLeads(int pageIndex, int pageSize)
        {
            List<Lead> leads = new List<Lead>();
            const string SpName = "sp_Get_PagingLeads";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { pageIndex, pageSize };
                reader = SqlHelper.ExecuteReaderNoTimeout(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    Lead lead = new Lead();

                    lead.RawData = !reader.IsDBNull(0) ? reader.GetString(0) : null;
                    if (lead.RawData.Length < 1000)
                    {
                        lead.LeadType = lead.RawData.Substring(81, 1).Trim();
                    }
                    else
                    {
                        lead.LeadType = lead.RawData.Substring(2791, 1).Trim();
                        lead.CallType = lead.RawData.Substring(2790, 1).Trim();
                    }

                    lead = GetLeadAdditional(lead);

                    leads.Add(lead);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get ordered leads", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return leads;
        }

        private DataTable ConstructDataTable()
        {
            DataTable table = new DataTable();

            for (int i = 0; i < 22; i++)
            {
                table.Columns.Add();
            }

            return table;
        }

        private Lead GetLeadAdditional(Lead lead)
        {
            if (lead != null)
            {
                if (lead.CallType == "B")
                {
                    lead.Telephone = lead.RawData.Substring(416, 3).Trim() + lead.RawData.Substring(419, 7).Trim();
                }
                else if (lead.CallType == "R")
                {
                    lead.Telephone = lead.RawData.Substring(426, 3).Trim() + lead.RawData.Substring(429, 7).Trim();
                }
                else
                {
                    // Nothing to do.
                }

                if (lead.LeadType == "B" || lead.LeadType == "N")
                {
                    lead.PreferCity = lead.RawData.Substring(54, 27).Trim();
                    lead.SubSequenceNumber = lead.RawData.Substring(92, 4).Trim();
                    lead.IndividualName = lead.RawData.Substring(96, 30).Trim();
                    lead.FirstName = lead.RawData.Substring(96, 30).Trim().Split(new char[] { ' ' }, StringSplitOptions.None)[0];
                    lead.LastName = lead.RawData.Substring(96, 30).Trim().Split(new char[] { ' ' },
                        StringSplitOptions.None)[lead.RawData.Substring(96, 30).Trim().Split(new char[] { ' ' },
                        StringSplitOptions.None).Length - 1];
                    lead.FirmName = lead.RawData.Substring(126, 30).Trim();
                    lead.StreetAddress = lead.RawData.Substring(156, 30).Trim();
                    lead.LeadCity = lead.RawData.Substring(186, 27).Trim();
                    lead.LeadState = lead.RawData.Substring(213, 2).Trim();
                    lead.Zip = lead.RawData.Substring(215, 10).Trim();
                    lead.Telephone = lead.RawData.Substring(225, 3).Trim() + lead.RawData.Substring(228, 7).Trim();
                    lead.SelDNC = lead.RawData.Substring(243, 1).Trim();
                    lead.BirthDate = lead.RawData.Substring(245, 7).Trim();
                    lead.HomeBaseInd = lead.RawData.Substring(282, 1).Trim();
                    lead.Latitude = lead.RawData.Substring(283, 9).Trim();
                    lead.Longitude = lead.RawData.Substring(292, 9).Trim();



                }
                else
                {
                    lead.IndividualName = lead.RawData.Substring(31, 30).Trim();
                    lead.BirthDate = lead.RawData.Substring(289, 7).Trim();
                    lead.SelDNC = lead.RawData.Substring(406, 1).Trim();
                    lead.PreferCity = lead.RawData.Substring(2763, 27).Trim();
                    lead.PersonStatus = lead.RawData.Substring(2833, 1).Trim();
                    lead.DncAfterDate = lead.RawData.Substring(2834, 8).Trim();
                    lead.PcLeadType = lead.RawData.Substring(3009, 1).Trim();
                    lead.PcName = lead.RawData.Substring(3010, 30).Trim();
                    lead.FirstName = lead.RawData.Substring(3010, 30).Trim().Split(new char[] { ' ' }, StringSplitOptions.None)[0];
                    lead.LastName = lead.RawData.Substring(3010, 30).Trim().Split(new char[] { ' ' },
                        StringSplitOptions.None)[lead.RawData.Substring(3010, 30).Trim().Split(new char[] { ' ' },
                        StringSplitOptions.None).Length - 1];
                    lead.StreetAddress = lead.RawData.Substring(3040, 30).Trim();
                    lead.LeadCity = lead.RawData.Substring(3070, 27).Trim();
                    lead.LeadState = lead.RawData.Substring(3097, 2).Trim();
                    lead.Zip = lead.RawData.Substring(3099, 10).Trim();
                    lead.Latitude = lead.RawData.Substring(3109, 9).Trim();
                    lead.Longitude = lead.RawData.Substring(3118, 9).Trim();
                    lead.BusPhone = lead.RawData.Substring(416, 10).Trim();
                    lead.ResPhone = lead.RawData.Substring(426, 10).Trim();
                }
            }

            return lead;
        }
    }
}
